package repo

import (
	"fmt"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"

	// "context"
	// "database/sql"
	"log"
)
type User struct{
    Id int `db:"user_id"`
	UserName string  `db:"username"`//用户姓名
	Password string `db:"password"`
	Email string    `db:"email"`
}
type Post struct{
    Pid int         `db:"pid"`
    Title string         `db:"title"`
    Slug string         `db:"slug"`
    Content string       `db:"content"`
    Markdown string      `db:"markdown"`
    Catrgoryid string     `db:"categoryid"`
    User_id int             `db:"user_id"`
    ViewCount int   `db:"viewCount"`
    Type int        `db:"Type"`
    CreateTime time.Time        `db:"createTime"`
    UpdateTime time.Time    `db:"updateTime"`
    Author string `db:"Author"`
}
type Comment struct{
    // post 和comment 一对多 外键关系
Pid int         `db:"pid"`
CName string       `db:"cname"` 
CTime time.Time    `db:"ctime"` 
CContent string    `db:"ccontent"` 
}
var db *sqlx.DB

func Init_db()(){
connStr := "root:shidaNO.1@tcp(127.0.0.1:3306)/myblog?parseTime=true"
    var err error
    db,err = sqlx.Connect("mysql",connStr)
    if err != nil{
        log.Fatalln(err.Error())
    }
    fmt.Println("连接数据库成功")
}

func Register(u User) int{
	sqlstr := `insert into user(username,password,email) values (?,?,?);`
	ret,err:= db.Exec(sqlstr,u.UserName,u.Password,u.Email)
	if err != nil {
    log.Fatalln(err.Error)
    }
   n ,err:=ret.LastInsertId()
   if err != nil {
    log.Fatalln(err.Error)
    }
    return int(n)
}

func Login(userid int,ps string) (bool,User){
    var u User
    sqlstrQuery:= `select user_id,username,password from user where user_id = ?;`   
    err := db.Get(&u,sqlstrQuery,userid)
    if err != nil {
        log.Fatalln(err.Error)
    }
    if u.Password== ps{
        return true ,u 
    }
    return false , u
}

func PostIndexList() ([]Post){
var posts []Post
// SELECT * FROM table_name LIMIT 10;
sqlstrQuery:= `SELECT * FROM post where pid <= :pid;`  
rows, err := db.NamedQuery(sqlstrQuery,Post{
    Pid : 10,
})
if err != nil {
    log.Fatalln(err.Error)
}
defer rows.Close()
for rows.Next(){
            var p Post
            err := rows.StructScan(&p)
            if err != nil {
                fmt.Printf("scan failed, err:%v\n", err)
                continue
            }
            posts = append(posts, p)
        }
return posts
}

func GetAPost(pid int) Post{
    sqlstrQuery:= `SELECT * FROM post where pid = ?;`  
    var p Post
    err := db.Get(&p,sqlstrQuery,pid)    
    if err != nil {
        log.Fatalln(err.Error)
    }
   return p
}
func DeletePost(pid int){
    sqlstrQuery:= `delete FROM post where pid = ?;`
    _,err := db.Exec(sqlstrQuery,pid) 
    if err != nil{
        log.Fatalln(err.Error)
    }
}
func GoHome(uid int) (User,[]Post){
    sqlStr := `SELECT * FROM user where user_id = ?;` 
    var u User
    err := db.Get(&u, sqlStr, uid)
    if err != nil{
        fmt.Println("visitHome出错")
        fmt.Println(err.Error())
        log.Fatalln(err.Error)
    }
    var posts []Post
// SELECT * FROM table_name LIMIT 10;
sqlStr  = `SELECT * FROM post where user_id = ?;`  
err = db.Select(&posts,sqlStr, uid)
if err != nil {
    fmt.Println("第二步出错了吗")
    log.Fatalln(err.Error)
}
return u,posts
}
func UpDatePost(p *Post){
    sqlstr := `update post set title=?,content=? where pid=?;`
    _ ,err:=db.Exec(sqlstr,p.Title,p.Content,p.Pid)
    if err != nil{
        fmt.Println(err.Error())
        log.Fatalln(err.Error)
    }
}
func QueryUserNameByUID(uid int) string{
    var u User
    sqlstrQuery:= `select user_id,username,password from user where user_id = ?;`   
    err := db.Get(&u,sqlstrQuery,uid)
    if err != nil {
        log.Fatalln(err.Error)
    }
    return u.UserName
}
func InsertPost(p *Post){
    sqlstr := `insert into post(title,slug,content,markdown,categoryid,user_id,viewCount,Type,Author) values (?,?,?,?,?,?,?,?,?);`
    _ ,err:=db.Exec(sqlstr,p.Title,p.Slug,p.Content,p.Markdown,p.Catrgoryid,p.User_id,p.ViewCount,p.Type,p.Author)
    if err != nil{
        log.Fatalln(err.Error)
    }
}